package service;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import beans.Branch;
import beans.DataCompany;
import beans.DataStatistics;
import beans.Terminal;

import dao.QueryDAO;

import utils.CalculateDate;
import utils.PageBean;




public class StatisticsService implements StatisticsServiceInterface {

	private DataStatistics basicData = new DataStatistics();

	private List<DataStatistics> statistics = new ArrayList<DataStatistics>();
	private List<Branch> branchs = null;
	private List<Terminal> terminals = null;
	private List<DataCompany> companyDatas = new ArrayList<DataCompany>();

	QueryDAO queryDAO = new QueryDAO();

	String sql = null;
	String hql = null;
	
	@Override
	public PageBean<?> queryDistributionData(int agentID, int curpage,int pageSize){
		List<?> list = null;
		List<?> subList = null;
		int fromIndex = (curpage - 1) * pageSize;
		int toIndex = curpage * pageSize;
		sql = "select p.package_user_mobile_num as 'userMobile',concat(b.branch_addr,t.terminal_name) as 'addr',count(p.package_id) as 'parcelSum',max(p.package_store_time) as 'storeTime',max(p.package_get_time) as 'getTime' from package p left join terminal t on p.package_terminal_id=t.terminal_id left join branch b on t.terminal_branch_id=b.branch_id where p.package_status=1 and b.branch_agent_id=? group by p.package_user_mobile_num,t.terminal_id order by 3 asc,4 desc";
		list = queryDAO.queryListSQL(sql,agentID);
		if(list.size()==0){
			subList = list;
		}else if(list.size()>0&&list.size()<=fromIndex){
			curpage=1;
			if(pageSize<=list.size()){
				subList = list.subList(0,pageSize);
			}else{
				subList = list.subList(0,list.size());
			}
		}else if(list.size()<=toIndex){
			subList = list.subList(fromIndex,list.size());
		}else{
			subList = list.subList(fromIndex,toIndex);
		}
		return new PageBean<>(curpage, pageSize,list.size(),subList);
	}
	
	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getCompanyData(int, int, int, java.sql.Timestamp, java.sql.Timestamp)
	 */
	@Override
	public PageBean<DataCompany> getCompanyData(int id, int page,
			int pageSize, Timestamp startTime, Timestamp endTime,String flag) {
		ArrayList<Object> list = getCompanyList(id, flag, page, pageSize, startTime, endTime);
		String[] msg = list.toString().trim().replace(" ", "").replace("{", "").replace("}", "").replace("[", "").replace("]", "").split(",");
		BigDecimal totalParcel = getTotalParcel(id, flag, startTime, endTime);
		DataCompany dataCompany = null;
		for(int i=0;i<list.size()*2;i+=2){
			dataCompany = new DataCompany();
			dataCompany.setCompanyName(msg[i].substring(15));
			dataCompany.setParcelNum(new BigDecimal(msg[i+1].substring(7)));
			dataCompany.setPercent(dataCompany.getParcelNum().divide(totalParcel,2,BigDecimal.ROUND_HALF_DOWN));
			companyDatas.add(dataCompany);
		}
		return new PageBean<DataCompany>(page, pageSize,
				distinctCompany(id, flag,startTime,endTime).intValue(), companyDatas);
	}

	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getBasicData(int, java.sql.Timestamp, java.sql.Timestamp)
	 */
	@Override
	public DataStatistics getBasicData(int id, Timestamp startTime,
			Timestamp endTime,String flag) {
		if("agent".equals(flag)){
			basicData.setBranchNum(getBranchNum(id));
			basicData.setTerminalNum(getTerminalNum(id, flag));
		}else if("branch".equals(flag)){
			basicData.setTerminalNum(getTerminalNum(id, flag));
		}
		basicData.setTotalDay(getTotalDay(id, flag, startTime, endTime));
		basicData.setTotalParcel(getTotalParcel(id, flag, startTime,endTime));
		basicData.setTakenParcel(getTakenParcel(id, flag, startTime,endTime));
		basicData.setUnTakenParcel(getUnTakenParcel(id, flag,startTime, endTime));
		basicData.setDealParcel(getDealParcel(id, flag, startTime,endTime));
		basicData.setTotalBox(getTotalBox(id, flag));
		basicData.setSuperBoxNum(getSuperBoxNum(id, flag));
		basicData.setBigBoxNum(getBigBoxNum(id, flag));
		basicData.setMediumBoxNum(getMediumBoxNum(id, flag));
		basicData.setSmallBoxNum(getSmallBoxNum(id, flag));
		if(basicData.getTotalParcel().intValue()==0){
			basicData.setTotalUseRate(new BigDecimal(0));
		}else{
			basicData.setTotalUseRate(basicData.getTotalParcel().divide(basicData.getTotalBox(), 2,BigDecimal.ROUND_HALF_DOWN).divide(basicData.getTotalDay(), 2,BigDecimal.ROUND_HALF_DOWN));
		}
		// basicData.setSuperUseRate(basicData.getTotalParcel().divide(basicData.getTotalBox()).divide(basicData.getTotalDay()));
		// basicData.setBigUseRate(basicData.getTotalParcel().divide(basicData.getTotalBox()).divide(basicData.getTotalDay()));
		// basicData.setMediumUseRate(mediumUseRate);
		// basicData.setSmallUseRate(basicData.getTotalParcel().divide(basicData.getTotalBox()).divide(basicData.getTotalDay()));
		return basicData;
	}

	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getBranchData(int, int, int, java.sql.Timestamp, java.sql.Timestamp)
	 */
	@Override
	public PageBean<DataStatistics> getDetailData(int id, int page,
			int pageSize, Timestamp startstime, Timestamp endtime,String flag) {
		if("branch".equals(flag)){
			branchs = getAgentBranchs(id, page, pageSize);
			for (Branch b : branchs) {
				basicData = new DataStatistics();
				basicData.setBranch(b);
				basicData.setTerminalNum(getTerminalNum(b.getBranchId(), flag));
				basicData.setTotalDay(getTotalDay(b.getBranchId(), flag,
						startstime, endtime));
				basicData.setTotalParcel(getTotalParcel(b.getBranchId(), flag,
						startstime, endtime));
				basicData.setTotalBox(getTotalBox(b.getBranchId(), flag));
				basicData.setLeft1Day(getLeftParcel(b.getBranchId(), flag, startstime, endtime, 0));
				basicData.setLeft3Day(getLeftParcel(b.getBranchId(), flag, startstime, endtime, 2));
				basicData.setLeft5Day(getLeftParcel(b.getBranchId(), flag, startstime, endtime, 4));
				if (basicData.getTotalParcel().intValue() == 0) {
					basicData.setTotalUseRate(new BigDecimal(0));
				} else {
					basicData.setTotalUseRate(basicData.getTotalParcel().divide(basicData.getTotalBox(), 2,BigDecimal.ROUND_HALF_DOWN).divide(basicData.getTotalDay(), 2,BigDecimal.ROUND_HALF_DOWN));
				}
				statistics.add(basicData);
			}
			Collections.sort(statistics, new Comparator<DataStatistics>() {
				@Override
				public int compare(DataStatistics o1, DataStatistics o2) {
					return o2.getTotalUseRate().compareTo(o1.getTotalUseRate());
				}
			});
			return new PageBean<DataStatistics>(page, pageSize,getBranchNum(id).intValue(), statistics);
		}else if("terminal".equals(flag)){
			terminals = getBranchTerminals(id, page, pageSize);
			for (Terminal t : terminals) {
				basicData = new DataStatistics();
				basicData.setTerminal(t);
				basicData.setTotalDay(getTotalDay(t.getTerminalId(), flag,
						startstime, endtime));
				basicData.setTotalParcel(getTotalParcel(t.getTerminalId(), flag,
						startstime, endtime));
				basicData.setTotalBox(getTotalBox(t.getTerminalId(), flag));
				basicData.setLeft1Day(getLeftParcel(t.getTerminalId(), flag, startstime, endtime, 0));
				basicData.setLeft3Day(getLeftParcel(t.getTerminalId(), flag, startstime, endtime, 2));
				basicData.setLeft5Day(getLeftParcel(t.getTerminalId(), flag, startstime, endtime, 4));
				if (basicData.getTotalParcel().intValue() == 0) {
					basicData.setTotalUseRate(new BigDecimal(0));
				} else {
					basicData.setTotalUseRate(basicData.getTotalParcel().divide(basicData.getTotalBox(), 2,BigDecimal.ROUND_HALF_DOWN).divide(basicData.getTotalDay(), 2,BigDecimal.ROUND_HALF_DOWN));
				}
				statistics.add(basicData);
			}
			Collections.sort(statistics, new Comparator<DataStatistics>() {
				@Override
				public int compare(DataStatistics o1, DataStatistics o2) {
					return o2.getTotalUseRate().compareTo(o1.getTotalUseRate());
				}
			});
			return new PageBean<DataStatistics>(page, pageSize,getTerminalNum(id,"branch").intValue(), statistics);
		}else{
			return null;
		}
	}
	@Override
	public BigInteger distinctCompany(int id,String flag, Timestamp startTime,Timestamp endTime){
		if ("agent".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(distinct p.package_belong) from package p,terminal t,branch b where b.branch_agent_id = ? and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id";
			}else{
				sql = "select count(distinct p.package_belong) from package p,terminal t,branch b where b.branch_agent_id = ? and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id and package_store_time>'"+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		if ("branch".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(distinct p.package_belong) from package p,terminal t where t.terminal_branch_id = ? and p.package_terminal_id=t.terminal_id";
			}else{
				sql = "select count(distinct p.package_belong) from package p,terminal t where t.terminal_branch_id = ? and p.package_terminal_id=t.terminal_id and package_store_time>'"+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		if ("terminal".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(distinct p.package_belong) from package p where p.package_terminal_id = ?";
			}else{
				sql = "select count(distinct p.package_belong) from package p where p.package_terminal_id = ? and package_store_time>'"+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		return (BigInteger)queryDAO.queryUniques(sql, id);
	}
	@Override
	public ArrayList<Object> getCompanyList(int id,String flag, int page,int pageSize, Timestamp startTime, Timestamp endTime){
		if ("agent".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "SELECT COUNT(*) AS `number`, `package_belong` FROM  package p,terminal t,branch b where b.branch_agent_id = ? and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id GROUP BY `package_belong` ORDER BY `number` desc";
			}else{
				sql = "SELECT COUNT(*) AS `number`, `package_belong` FROM  package p,terminal t,branch b where b.branch_agent_id = ? and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'" +" GROUP BY `package_belong` ORDER BY `number` desc";
			}
		}
		if ("branch".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "SELECT COUNT(*) AS `number`, `package_belong` FROM  package p,terminal t where t.terminal_branch_id = ? and p.package_terminal_id=t.terminal_id GROUP BY `package_belong` ORDER BY `number` desc";
			}else{
				sql = "SELECT COUNT(*) AS `number`, `package_belong` FROM  package p,terminal t where t.terminal_branch_id = ? and p.package_terminal_id=t.terminal_id and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'" +" GROUP BY `package_belong` ORDER BY `number` desc";
			}
		}
		if ("terminal".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "SELECT COUNT(*) AS `number`, `package_belong` FROM  package p where p.package_terminal_id = ? GROUP BY `package_belong` ORDER BY `number` desc";
			}else{
				sql = "SELECT COUNT(*) AS `number`, `package_belong` FROM  package p where p.package_terminal_id = ? and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'" +" GROUP BY `package_belong` ORDER BY `number` desc";
			}
		}
		ArrayList<Object> list = (ArrayList<Object>) queryDAO.queryByPages(sql, page, pageSize, id);
		while (page > 1 && list.size() <= 0) {
			page = page - 1;
			list = (ArrayList<Object>) queryDAO.queryByPage(sql, page, pageSize, id);
		}
		return list;
	}

	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getAgentBranchs(int, int, int)
	 */
	@Override
	public List<Branch> getAgentBranchs(int agentID, int page, int pageSize) {
		hql = "from Branch b where b.agent.id = ? or b.agent.parentID = ? and b.status = 0";
		List<Branch> list = queryDAO.queryByPage(hql, page, pageSize, agentID,agentID);
		while (page > 1 && list.size() <= 0) {
			page = page - 1;
			list = queryDAO.queryByPage(hql, page, pageSize, agentID,agentID);
		}
		return list;
	}
	@Override
	public List<Terminal> getBranchTerminals(int brnachID, int page, int pageSize) {
		hql = "from Terminal t where t.branch.branchId = ? and t.status = 0";
		List<Terminal> list = queryDAO.queryByPage(hql, page, pageSize, brnachID);
		while (page > 1 && list.size() <= 0) {
			page = page - 1;
			list = queryDAO.queryByPage(hql, page, pageSize, brnachID);
		}
		return list;
	}

	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getTerminalNum(int, java.lang.String)
	 */
	@Override
	public BigDecimal getTerminalNum(int id, String flag) {
		if ("agent".equals(flag)) {
			sql = "select count(t.terminal_id) from terminal t left join branch b on b.branch_id=t.terminal_branch_id left join agent a on a.agent_id=b.branch_agent_id where b.branch_agent_id = ? or a.agent_parentID = ? and t.terminal_status=0";
			return new BigDecimal((BigInteger) queryDAO.queryUniques(sql,id,id));
		}
		if ("branch".equals(flag)) {
			sql = "select count(*) from terminal t where t.terminal_branch_id = ? and t.terminal_status=0";
			return new BigDecimal((BigInteger) queryDAO.queryUniques(sql,id));
		}
		return null;
	}

	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getBranchNum(int)
	 */
	@Override
	public BigDecimal getBranchNum(int agentID) {
		sql = "select count(b.branch_id) from branch b left join agent a on a.agent_id=b.branch_agent_id where b.branch_agent_id = ? or a.agent_parentID = ? and b.branch_status=0";
		return new BigDecimal((BigInteger) queryDAO.queryUniques(sql, agentID,agentID));
	}

	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getTotalDay(int, java.lang.String, java.sql.Timestamp, java.sql.Timestamp)
	 */
	@Override
	public BigDecimal getTotalDay(int id, String flag, Timestamp startTime,
			Timestamp endTime) {
		if ("agent".equals(flag)) {
			sql = "select package_store_time from package p,terminal t,branch b where b.branch_agent_id = ? and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id order by package_store_time asc";
		}
		if ("branch".equals(flag)) {
			sql = "select package_store_time from package p,terminal t where t.terminal_branch_id = ? and p.package_terminal_id=t.terminal_id order by package_store_time asc";
		}
		if ("terminal".equals(flag)) {
			sql = "select package_store_time from package p where p.package_terminal_id=? order by package_store_time asc";
		}
		Object object = queryDAO.queryUniques(sql, id);
		BigDecimal bigDecimal = null;
		if (object == null) {
			return new BigDecimal(0);
		} else {
			long now;
			long before;
			if (startTime != null && endTime != null) {
				now = new Date().getTime();
				now = endTime.getTime() > now ? now : endTime.getTime();
				before = startTime.getTime() > ((Timestamp) object).getTime() ? startTime
						.getTime() : ((Timestamp) object).getTime();
				basicData.setsTime(new Timestamp(before).toString().substring(0, 19));
				basicData.seteTime(new Timestamp(now).toString().substring(0, 19));
				bigDecimal = new BigDecimal((now - before) / (3600 * 24 * 1000));
				if(bigDecimal.intValue()==0){
					bigDecimal = new BigDecimal(1);
				}
				return bigDecimal;
			} else {
				now = new Date().getTime();
				before = ((Timestamp) object).getTime();
				basicData.setsTime(new Timestamp(before).toString().substring(0, 19));
				basicData.seteTime(new Timestamp(now).toString().substring(0, 19));
				bigDecimal = new BigDecimal((now - before) / (3600 * 24 * 1000));
				if(bigDecimal.intValue()==0){
					bigDecimal = new BigDecimal(1);
				}
				return bigDecimal;
			}
		}
	}

	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getTotalParcel(int, java.lang.String, java.sql.Timestamp, java.sql.Timestamp)
	 */
	@Override
	public BigDecimal getTotalParcel(int id, String flag, Timestamp startTime,
			Timestamp endTime) {
		if ("agent".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p,terminal t,branch b where b.branch_agent_id = ? and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id";
			} else {
				sql = "select count(*) from package p,terminal t,branch b where b.branch_agent_id = ? and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		if ("branch".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p,terminal t where t.terminal_branch_id=? and p.package_terminal_id=t.terminal_id";
			} else {
				sql = "select count(*) from package p,terminal t where t.terminal_branch_id=? and p.package_terminal_id=t.terminal_id and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		if ("terminal".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p where p.package_terminal_id=?";
			} else {
				sql = "select count(*) from package p where p.package_terminal_id=? and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		return new BigDecimal((BigInteger) queryDAO.queryUniques(sql, id));
	}
	/**
	 * 查询滞留包裹
	 * @param id
	 * @param flag
	 * @param startTime
	 * @param endTime
	 * @param day
	 * @return
	 */
	public BigDecimal getLeftParcel(int id, String flag, Timestamp startTime,Timestamp endTime,int day) {
		Timestamp leftTime = new Timestamp(CalculateDate.getDateBeforeByDay(new Date(), day).getTime());
		if(startTime != null && endTime != null){
			if(leftTime.getTime()<endTime.getTime()){
				endTime = leftTime;
			}
//			if(leftTime.getTime()>=startTime.getTime()&&leftTime.getTime()<=endTime.getTime()){
//				endTime = leftTime;
//			}
//			if(leftTime.getTime()<startTime.getTime()){
//				endTime = leftTime;
//			}
		}
		if ("agent".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p,terminal t,branch b where b.branch_agent_id = ? and p.package_status in (0,2) and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id and p.package_store_time<'"+leftTime+"'";
			} else {
				sql = "select count(*) from package p,terminal t,branch b where b.branch_agent_id = ? and p.package_status in (0,2) and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		if ("branch".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p,terminal t where t.terminal_branch_id=? and p.package_status in (0,2) and p.package_terminal_id=t.terminal_id and p.package_store_time<'"+leftTime+"'";
			} else {
				sql = "select count(*) from package p,terminal t where t.terminal_branch_id=? and p.package_status in (0,2) and p.package_terminal_id=t.terminal_id and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		if ("terminal".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p where p.package_terminal_id=? and p.package_status in (0,2) and p.package_store_time<'"+leftTime+"'";
			} else {
				sql = "select count(*) from package p where p.package_terminal_id=? and p.package_status in (0,2) and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		return new BigDecimal((BigInteger) queryDAO.queryUniques(sql, id));
		/*Object object = queryDAO.queryUniques(sql, id);
		BigDecimal bigDecimal = null;
		if (object == null) {
			return new BigDecimal(0);
		} else {
			long now;
			long before;
			if (startTime != null && endTime != null) {
				now = new Date().getTime();
				now = endTime.getTime() > now ? now : endTime.getTime();
				before = startTime.getTime() > ((Timestamp) object).getTime() ? startTime
						.getTime() : ((Timestamp) object).getTime();
				basicData.setsTime(new Timestamp(before));
				basicData.seteTime(new Timestamp(now));
				bigDecimal = new BigDecimal((now - before) / (3600 * 24 * 1000));
				if(bigDecimal.intValue()==0){
					bigDecimal = new BigDecimal(1);
				}
				return bigDecimal;
			} else {
				now = new Date().getTime();
				before = ((Timestamp) object).getTime();
				basicData.setsTime(new Timestamp(before));
				basicData.seteTime(new Timestamp(now));
				bigDecimal = new BigDecimal((now - before) / (3600 * 24 * 1000));
				if(bigDecimal.intValue()==0){
					bigDecimal = new BigDecimal(1);
				}
				return bigDecimal;
			}
		}*/
	}

	// public BigDecimal getSuperParcel(int agentID){
	// sql =
	// "select count(*) from package p,terminal t,branch b,boxstatus x where b.branch_agent_id = ? and x.boxStatus_boxTypeID = 1 and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id and x.boxStatus_terminalID=t.terminal_id and p.package_box_id=x.boxStatus_boxNum";
	// return new BigDecimal((BigInteger)queryDAO.queryUniques(sql, agentID));
	// }
	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getTakenParcel(int, java.lang.String, java.sql.Timestamp, java.sql.Timestamp)
	 */
	@Override
	public BigDecimal getTakenParcel(int id, String flag, Timestamp startTime,
			Timestamp endTime) {
		if ("agent".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p,terminal t,branch b where b.branch_agent_id = ? and p.package_status = 1 and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id";
			} else {
				sql = "select count(*) from package p,terminal t,branch b where b.branch_agent_id = ? and p.package_status = 1 and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		if ("branch".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p,terminal t where t.terminal_branch_id=? and p.package_status = 1 and p.package_terminal_id=t.terminal_id";
			}else{
				sql = "select count(*) from package p,terminal t where t.terminal_branch_id=? and p.package_status = 1 and p.package_terminal_id=t.terminal_id and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		if ("terminal".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p where p.package_terminal_id=? and p.package_status = 1";
			}else{
				sql = "select count(*) from package p where p.package_terminal_id=? and p.package_status = 1 and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		return new BigDecimal((BigInteger) queryDAO.queryUniques(sql, id));
	}

	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getUnTakenParcel(int, java.lang.String, java.sql.Timestamp, java.sql.Timestamp)
	 */
	@Override
	public BigDecimal getUnTakenParcel(int id, String flag,
			Timestamp startTime, Timestamp endTime) {
		if ("agent".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p,terminal t,branch b where b.branch_agent_id = ? and p.package_status in (0,2) and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id";
			} else {
				sql = "select count(*) from package p,terminal t,branch b where b.branch_agent_id = ? and p.package_status in (0,2) and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		if ("branch".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p,terminal t where t.terminal_branch_id=? and p.package_status in (0,2) and p.package_terminal_id=t.terminal_id";
			}else{
				sql = "select count(*) from package p,terminal t where t.terminal_branch_id = ? and p.package_status in (0,2) and p.package_terminal_id=t.terminal_id and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		if ("terminal".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p where p.package_terminal_id=? and p.package_status in (0,2)";
			}else{
				sql = "select count(*) from package p where p.package_terminal_id = ? and p.package_status in (0,2) and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		return new BigDecimal((BigInteger) queryDAO.queryUniques(sql, id));
	}

	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getDealParcel(int, java.lang.String, java.sql.Timestamp, java.sql.Timestamp)
	 */
	@Override
	public BigDecimal getDealParcel(int id, String flag, Timestamp startTime,
			Timestamp endTime) {
		if ("agent".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p,terminal t,branch b where b.branch_agent_id = ? and p.package_status in (5,6) and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id";
			} else {
				sql = "select count(*) from package p,terminal t,branch b where b.branch_agent_id = ? and p.package_status in (5,6) and p.package_terminal_id=t.terminal_id and t.terminal_branch_id=b.branch_id and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		if ("branch".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p,terminal t where t.terminal_branch_id=? and p.package_status in (5,6) and p.package_terminal_id=t.terminal_id";
			}else{
				sql = "select count(*) from package p,terminal t where t.terminal_branch_id = ? and p.package_status in (5,6) and p.package_terminal_id=t.terminal_id and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		if ("terminal".equals(flag)) {
			if (startTime == null && endTime == null) {
				sql = "select count(*) from package p where p.package_terminal_id=? and p.package_status in (5,6)";
			}else{
				sql = "select count(*) from package p where p.package_terminal_id = ? and p.package_status in (5,6) and package_store_time>'"
						+ startTime + "' and package_store_time<'" + endTime+"'";
			}
		}
		return new BigDecimal((BigInteger) queryDAO.queryUniques(sql, id));
	}

	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getTotalBox(int, java.lang.String)
	 */
	@Override
	public BigDecimal getTotalBox(int id, String flag) {
		if ("agent".equals(flag)) {
			sql = "select count(*) from boxstatus x,terminal t,branch b where b.branch_agent_id = ? and x.boxStatus_terminalID=t.terminal_id and t.terminal_branch_id=b.branch_id";
		}
		if ("branch".equals(flag)) {
			sql = "select count(*) from boxstatus x,terminal t where t.terminal_branch_id=? and x.boxStatus_terminalID=t.terminal_id";
		}
		if ("terminal".equals(flag)) {
			sql = "select count(*) from boxstatus x where x.boxStatus_terminalID=?";
		}
		return new BigDecimal((BigInteger) queryDAO.queryUniques(sql, id));
	}

	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getSuperBoxNum(int, java.lang.String)
	 */
	@Override
	public BigDecimal getSuperBoxNum(int id, String flag) {
		if ("agent".equals(flag)) {
			sql = "select count(*) from boxstatus x,terminal t,branch b where b.branch_agent_id = ? and x.boxStatus_boxTypeID=1 and x.boxStatus_terminalID=t.terminal_id and t.terminal_branch_id=b.branch_id";
		}
		if ("branch".equals(flag)) {
			sql = "select count(*) from boxstatus x,terminal t where t.terminal_branch_id=? and x.boxStatus_boxTypeID=1 and x.boxStatus_terminalID=t.terminal_id";
		}
		if ("terminal".equals(flag)) {
			sql = "select count(*) from boxstatus x where x.boxStatus_terminalID=? and x.boxStatus_boxTypeID=1";
		}
		return new BigDecimal((BigInteger) queryDAO.queryUniques(sql, id));
	}

	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getBigBoxNum(int, java.lang.String)
	 */
	@Override
	public BigDecimal getBigBoxNum(int id, String flag) {
		if ("agent".equals(flag)) {
			sql = "select count(*) from boxstatus x,terminal t,branch b where b.branch_agent_id = ? and x.boxStatus_boxTypeID=2 and x.boxStatus_terminalID=t.terminal_id and t.terminal_branch_id=b.branch_id";
		}
		if ("branch".equals(flag)) {
			sql = "select count(*) from boxstatus x,terminal t where t.terminal_branch_id=? and x.boxStatus_boxTypeID=2 and x.boxStatus_terminalID=t.terminal_id";
		}
		if ("terminal".equals(flag)) {
			sql = "select count(*) from boxstatus x where x.boxStatus_terminalID=? and x.boxStatus_boxTypeID=2";
		}
		return new BigDecimal((BigInteger) queryDAO.queryUniques(sql, id));
	}

	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getMediumBoxNum(int, java.lang.String)
	 */
	@Override
	public BigDecimal getMediumBoxNum(int id, String flag) {
		if ("agent".equals(flag)) {
			sql = "select count(*) from boxstatus x,terminal t,branch b where b.branch_agent_id = ? and x.boxStatus_boxTypeID=3 and x.boxStatus_terminalID=t.terminal_id and t.terminal_branch_id=b.branch_id";
		}
		if ("branch".equals(flag)) {
			sql = "select count(*) from boxstatus x,terminal t where t.terminal_branch_id=? and x.boxStatus_boxTypeID=3 and x.boxStatus_terminalID=t.terminal_id";
		}
		if ("terminal".equals(flag)) {
			sql = "select count(*) from boxstatus x where x.boxStatus_terminalID=? and x.boxStatus_boxTypeID=3";
		}
		return new BigDecimal((BigInteger) queryDAO.queryUniques(sql, id));
	}

	/* (non-Javadoc)
	 * @see Service.StatisticsServiceInterface#getSmallBoxNum(int, java.lang.String)
	 */
	@Override
	public BigDecimal getSmallBoxNum(int id, String flag) {
		if ("agent".equals(flag)) {
			sql = "select count(*) from boxstatus x,terminal t,branch b where b.branch_agent_id = ? and x.boxStatus_boxTypeID=4 and x.boxStatus_terminalID=t.terminal_id and t.terminal_branch_id=b.branch_id";
		}
		if ("branch".equals(flag)) {
			sql = "select count(*) from boxstatus x,terminal t where t.terminal_branch_id=? and x.boxStatus_boxTypeID=4 and x.boxStatus_terminalID=t.terminal_id";
		}
		if ("terminal".equals(flag)) {
			sql = "select count(*) from boxstatus x where x.boxStatus_terminalID=? and x.boxStatus_boxTypeID=4";
		}
		return new BigDecimal((BigInteger) queryDAO.queryUniques(sql, id));
	}

}